using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace EntLibContrib.Data.SQLite.Tests
{
    [TestClass]
    
    public class SQLiteParameterizedSqlStringFixture
    {
        private Database db;

        [TestInitialize]
        public void SetUp()
        {
            DatabaseProviderFactory factory = new DatabaseProviderFactory(TestConfigurationSource.CreateConfigurationSource());
            db = factory.Create("SQLiteNorthwind");
        }

        [TestMethod, DeploymentItem("EntLibContrib.Data.SqLite.Tests.dll.config"), DeploymentItem("Northwind.db")]
        public void ExecuteSqlStringCommandWithParameters()
        {
            string sql = "select * from Region where RegionID = @Param1 and RegionDescription like @Param2";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Param1", DbType.Int32, 1);
            db.AddInParameter(cmd, "@Param2", DbType.String, "Eastern%");
            DataSet ds = db.ExecuteDataSet(cmd);
            Assert.AreEqual(1, ds.Tables[0].Rows.Count);
        }

        [TestMethod, DeploymentItem("EntLibContrib.Data.SqLite.Tests.dll.config"), DeploymentItem("Northwind.db")]
        [ExpectedException(typeof(SQLiteException))]
        public void ExecuteSqlStringCommandWithNotEnoughParameterValues()
        {
            string sql = "select * from Region where RegionID=@Param1 and RegionDescription=@Param2";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Param1", DbType.Int32, 1);
            db.ExecuteDataSet(cmd);
        }

        [TestMethod, DeploymentItem("EntLibContrib.Data.SqLite.Tests.dll.config"), DeploymentItem("Northwind.db")]
        [ExpectedException(typeof(SQLiteException))]
        [Ignore] //SQLite does not query number of parameters; allows extra
        public void ExecuteSqlStringCommandWithTooManyParameterValues()
        {
            string sql = "select * from Region where RegionID=@Param1 and RegionDescription=@Param2";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Param1", DbType.Int32, 1);
            db.AddInParameter(cmd, "@Param2", DbType.String, "Eastern");
            db.AddInParameter(cmd, "@Param2", DbType.String, "Western");
            db.ExecuteDataSet(cmd);
        }

        [TestMethod, DeploymentItem("EntLibContrib.Data.SqLite.Tests.dll.config"), DeploymentItem("Northwind.db")]
        [Ignore] //Syntax not valid for SQLite
        public void ExecuteSqlStringCommandWithDoubleAtSymbol()
        {
            string sql = "select @@SERVERNAME, * from Region where RegionID=@ID and @@MAX_CONNECTIONS>1 and RegionDescription=@Desc";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@ID", DbType.Int32, 1);
            db.AddInParameter(cmd, "@Desc", DbType.String, "Eastern");
            DataSet ds = db.ExecuteDataSet(cmd);
            Assert.AreEqual(1, ds.Tables[0].Rows.Count);
        }

        [TestMethod, DeploymentItem("EntLibContrib.Data.SqLite.Tests.dll.config"), DeploymentItem("Northwind.db")]
        public void ExecuteSqlStringWithoutParametersButWithValues()
        {
            string sql = "select * from Region";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Param1", DbType.Int32, 1);
            db.AddInParameter(cmd, "@Param2", DbType.String, "Eastern");
            DataSet ds = db.ExecuteDataSet(cmd);
            Assert.AreEqual(4, ds.Tables[0].Rows.Count);
        }
    }
}
